import socket,threading,socketserver,json,time
import pymysql

class ThreadedTCPRequestHandler(socketserver.BaseRequestHandler):

    def handle(self):
        indata = str(self.request.recv(1024), 'ascii')
        cur_thread = threading.current_thread()
        print(indata)
        #indata=self.data
        bjson=True
        # try if indata is in json format.
        # only process indata, if in json
        try:
          test=json.loads(indata)
        except:
          bjson=False
        else:
          # indata must have a payload entry
          if "payload" in test:
            pwf = open("my.cnf","r")
            pwj = json.loads(pwf.read())
            # get credential for sql server and open connection
            try:
              mydb=pymysql.connect(host="localhost",user=pwj['user'],passwd=pwj['password'],database=pwj['database'])
            except:
              print("Could not connect to sql server! Quitting")
            else:
              # only process if sql connection could be open
              sqlinsert="insert into datain (time,device,var,sensor,i2c,value) values ({0:d},'{1:s}','{2:s}','{3:s}',{4:d},{5:d})"

              datasource=self.client_address[0]
              if "device" in test:
                datasource=test['device'].translate(' ./:;*|')
                datasource=datasource[:64]
              multi=1
              if "mult" in test:
                multi=int(test['mult'])
              mycursor=mydb.cursor()
              payload=test['payload']
              for x,y in payload.items():
                # remove unwanted characters from variable name
                varx=x.translate(' ./:;*!')
                varx=varx[:64]
                value=0
                if "value" in y:
                  value=int(y['value'])
                  sensor=""
                  if "time" in y:
                    datatime=int(y['time'])
                  else:
                    datatime=int(1000*time.time())
                  if "sensor" in y:
                    sensor=y['sensor']
                    sensor=sensor.translate(' ./:;*!')
                    sensor=sensor[:32]
                  i2c=0
                  if "i2c" in y:
                    i2c=int(y['i2c'])
                  try:
                    mycursor.execute(sqlinsert.format(datatime,datasource,varx,sensor,i2c,int(multi*value)))
                  except:
                    print("Error sql statement")
                    print(sqlinsert.format(datatime,datasource,varx,sensor,i2c,int(multi*value)))
                try:
                  mydb.commit()
                except:
                  print("Could not commit")
              mydb.close()
        # just send back the same data, but upper-cased
#        self.request.send("Done")
        

class ThreadedTCPServer(socketserver.ThreadingMixIn, socketserver.TCPServer):
    pass

if __name__ == "__main__":
    # Port 0 means to select an arbitrary unused port
    HOST, PORT = "", 24048

    server = ThreadedTCPServer((HOST, PORT), ThreadedTCPRequestHandler)

    # Start a thread with the server -- that thread will then start one
    # more thread for each request
    server_thread = threading.Thread(target=server.serve_forever)
    # Exit the server thread when the main thread terminates
    server_thread.daemon = True
    server_thread.start()
    print("Server loop running in thread:", server_thread.name)

    server.serve_forever()
    
    server.shutdown()
    server.server_close()
